<?php
	function connect(){
		$db_handle = pg_connect(" dbname=template_base user=tadmin password=tadmin ");
		if (!$db_handle){
			die('Could not connect: ' . pg_last_error());
		}
		return $db_handle;
	}
	
	
	//UWAGA, zgapiane z jakiejs strony, dobre do debugu
	//usun gdy aplikacja bedzie gotowa
	
	function table_print($result)
	{
	   $numrows = pg_num_rows($result);
	   $fnum = pg_num_fields($result);

	   echo "<table border width='100%'>";
	   echo "<tr>";

	   for ($x = 0; $x < $fnum; $x++) {
		   echo "<td><b>";
		   echo strtoupper(pg_field_name($result, $x));
		   echo "</b></td>";
	   }

	   echo "</tr>";

	   for ($i = 0; $i < $numrows; $i++) {
		   $row = pg_fetch_object($result, $i);
		   echo "<tr align='center'>";
		   for ($x = 0; $x < $fnum; $x++) {
	   $fieldname = pg_field_name($result, $x);
	   echo "<td>";
	   echo $row->$fieldname;
	   echo "</td>";
		   }
		   echo"</tr>";
	   }
	   echo "</table>";
	   
	   return 0;
	}
	
	
	function check_password($user,$password){
		$db_handle = connect();
		
		//$query = 'SELECT * FROM USERS;';
		
		$query = "SELECT name FROM USERS WHERE name = '".$user."' AND pass = '".$password."';";
		
		$result = pg_query($db_handle, $query);
		$numrows = pg_num_rows($result);
		
		if ($numrows>0) return true;
		return false;
	}
	
	// FUNKCJE ZWIAZANE Z KATEGORIAMI

	function add_category($category_name, $parentSectionID){
		$db_handle = connect();
		
		$query = "INSERT INTO SECTION (sectionName, parentSectionID) VALUES ('$category_name', '$parentSectionID');";
		pg_query($db_handle, $query);
		// echo $category_name . "oraz" . $parentSectionID;
	}
	
	function delete_category($del_cat){
		$db_handle = connect();
		
		$array = get_sonSections($del_cat, 0);
		
		foreach ($array as $section){
			$del_cat = get_sectionID($section[0]);
			$query = "DELETE FROM SECTION WHERE sectionID = '$del_cat'";
			pg_query($db_handle, $query);
		
			$query = "UPDATE PROBLEM SET sectionID = -1 WHERE sectionID = '$del_cat'";
			pg_query($db_handle, $query);	
		}
	}
	
	function get_section_name($sectionID){
		$db_handle = connect();

		$query = "SELECT sectionName FROM SECTION WHERE sectionID = '$sectionID';";
		$result = pg_query($db_handle, $query);
		
		return pg_fetch_result($result, 0, 0);
	}
	
	function get_sectionID($name){
		$db_handle = connect();
		
		$query = "SELECT sectionID FROM SECTION WHERE sectionName = '$name';";
		$result = pg_query($db_handle, $query);
		
		return pg_fetch_result($result, 0, 0);
	}
	
	function get_sonSections($parentID, $level){
		$db_handle = connect();
		
		$query = "SELECT sectionID FROM SECTION WHERE parentSectionID = '$parentID' ORDER BY sectionName;";
		$result = pg_query($db_handle, $query);
		$numrows = pg_num_rows($result);
		
		$array = array();
		
		$category = get_section_name($parentID);
		$array[] = array($category, $level, $parentID);
		
		for ($i = 0; $i < $numrows; $i++){
			$array2 = get_sonSections(pg_fetch_result($result, $i, 0), $level + 1);
			foreach ($array2 as $section)
				$array[] = array($section[0], $section[1], $section[2]);
		}
		
		return $array;
	}
	
	function get_all_sections(){
		return get_sonSections(-1, 0);
	}
	
	function normalize($name, $level){
		$res = "";
		for ($i = 0; $i < $level; $i++)
			$res .= "- ";
		return $res . $name;
	}
	
	function get_all_section_names(){
		$array = get_all_sections();
		
		$result = array();
		
		foreach ($array as $section)
			$result[] = $section[1];
		
		return $result;
	}
	
	//FUNKCJE ZWIAZANIE Z PROBLEMAMI
	
	function add_problem($problem_name, $sectionID, $problemDescription, $main){
		$db_handle = connect();
		
		//$main = addslashes($main);
		$query = "INSERT INTO PROBLEM (problemName, sectionID, problemDescription, main, problemStatus) VALUES ('$problem_name', '$sectionID', '$problemDescription', '$main', 'TODO');";
		pg_query($db_handle, $query);
	}
	
	function get_all_problemID(){
		echo 'OK<br>';
		$db_handle = connect();
		
		$query = "SELECT problemID FROM PROBLEM ORDER BY problemName;";
		$result = pg_query($db_handle, $query);
		
		$numrows = pg_num_rows($result);
		echo $numrows.'<br>';
		
		$array = array();
		
		for ($i = 0; $i < $numrows; $i++) {
			$array[] = pg_fetch_result($result, $i, 0);
		}
		
		return $array;
	}
	
	function get_section_problems($sectionID){
		$db_handle = connect();
		
		$query = "SELECT problemID FROM PROBLEM where sectionID = $sectionID;";
		$result = pg_query($db_handle, $query);
		
		$numrows = pg_num_rows($result);
		
		$array = array();
		
		for ($i = 0; $i < $numrows; $i++) {
			$array[] = pg_fetch_result($result, $i, 0);
		}
		
		return $array;
	}
	
	//zakladam ze problemID jest poprawny
	function get_problem_name($problemID){
		$db_handle = connect();
		
		$query = "SELECT problemName FROM PROBLEM WHERE problemID = $problemID ;";
		$result = pg_query($db_handle, $query);
		
		return pg_fetch_result($result, 0, 0);
	}
	
	//zakladam ze problemID jest poprawny
	function get_problem_section_id($problemID){
		$db_handle = connect();
		
		$query = "SELECT sectionID FROM PROBLEM WHERE problemID = $problemID ;";
		$result = pg_query($db_handle, $query);
		
		return pg_fetch_result($result, 0, 0);
	}
	
	function get_problem_description($problemID){
		$db_handle = connect();
		
		$query = "SELECT problemDescription FROM PROBLEM WHERE problemID = $problemID ;";
		$result = pg_query($db_handle, $query);
		
		return pg_fetch_result($result, 0, 0);
	}
	
	function get_problem_main($problemID){
		$db_handle = connect();
		
		$query = "SELECT main FROM PROBLEM WHERE problemID = $problemID ;";
		$result = pg_query($db_handle, $query);
		
		return pg_fetch_result($result, 0, 0);
	}
	
	function get_problem_status($problemID){
		$db_handle = connect();
		
		$query = "SELECT problemStatus FROM PROBLEM WHERE problemID = $problemID ;";
		$result = pg_query($db_handle, $query);
		
		return pg_fetch_result($result, 0, 0);
	}
	
	function get_problem_owner($problemID){
		$db_handle = connect();
		
		$query = "SELECT owner FROM PROBLEM WHERE problemID = '$problemID';";
		$result = pg_query($db_handle, $query);
		
		return pg_fetch_result($result, 0, 0);
	}
	
	function get_problem_solutions($problemID){
		$db_handle = connect();
		
		$array = array();
		$query = "SELECT solutionID FROM SOLUTION WHERE problemID = $problemID;";
		$result = pg_query($db_handle, $query);
		$numrows = pg_num_rows($result);
		
		for ($i = 0; $i < $numrows; $i++) {
			$array[] = pg_fetch_result($result, $i, 0);
		}
		
		return $array;
	}
	
	function get_all_owners(){
		$db_handle = connect();
		
		$array = array();
		$array[] = "nobody";
		$query = "SELECT name FROM USERS ORDER BY name;";
		$result = pg_query($db_handle, $query);
		$numrows = pg_num_rows($result);
		
		for ($i = 0; $i < $numrows; $i++) {
			$array[] = pg_fetch_result($result, $i, 0);
		}
		
		return $array;
	}
	
	function get_solution_problemID($solutionID){
		$db_handle = connect();
		
		$query = "SELECT problemID FROM SOLUTION WHERE solutionID = $solutionID ;";
		$result = pg_query($db_handle, $query);
		
		return pg_fetch_result($result, 0, 0);
	}
	
	function get_solution_status($solutionID){
		$db_handle = connect();
		
		$query = "SELECT status FROM SOLUTION WHERE solutionID = $solutionID ;";
		$result = pg_query($db_handle, $query);
		
		return pg_fetch_result($result, 0, 0);
	}	
	
	function change_problem_content($problemID, $problemName, $sectionID, $problemDescription, $main, $problemStatus, $owner){
		$db_handle = connect();
		
		//$main = addslashes($main);
		$query = "UPDATE PROBLEM SET problemName = '$problemName', sectionID = '$sectionID', problemDescription = '$problemDescription', main = '$main', problemStatus = '$problemStatus', owner = '$owner' WHERE problemID = '$problemID'";
		pg_query($db_handle, $query);
	}
	
	function add_new_solution($problemID){
		$db_handle = connect();
		
		$query = "INSERT INTO SOLUTION (problemID, status) VALUES ('$problemID', 'NOT CHECKED');";
		pg_query($db_handle, $query);
		
		$query = "SELECT MAX(solutionID) FROM SOLUTION;";
		$result = pg_query($db_handle, $query);
		
		return pg_fetch_result($result, 0, 0);
	}
	
	function link_to_problem($problemID){
		echo "<a href=\"index.php?location=edytuj_problem&problem_id=".$problemID."\">".get_problem_name($problemID)."</a>";
	}
	
	function status($status){
		echo "<td bgcolor=";
		if($status == "ACCEPTED") echo "\"lime\">";
		else if($status == "WRONG ANSWER") echo "\"red\">";
		else if($status == "TIME LIMIT EXCEEDED") echo "\"orange\">";
		else if($status == "RUNTIME ERROR") echo "\"yellow\">";
		else echo "\"violet\">";
		echo $status;
	}
	
	function print_status(){
		$db_handle = connect();
		$query = "SELECT solutionID AS Nr, problemID AS PROBLEM, status AS Status, time AS Czas FROM SOLUTION ORDER BY -solutionID;";
		$result = pg_query($db_handle, $query);
		
		// wypisanie wyniku
		$numrows = pg_num_rows($result);
		$fnum = pg_num_fields($result);
		
		$array = array();

		echo "<table border width='100%'>";
		echo "<tr align='center'>";

		for ($x = 0; $x < $fnum; $x++) {
		   $array[] = strtoupper(pg_field_name($result, $x));
		   echo "<td><b><font size='5'>";
			echo strtoupper(pg_field_name($result, $x));
			echo "</font></b></td>";
		}

		echo "</tr>";

		for ($i = 0; $i < $numrows; $i++) {
		   $row = pg_fetch_object($result, $i);
		   echo "<tr align='center'>";
		   for ($x = 0; $x < $fnum; $x++) {
		$fieldname = pg_field_name($result, $x);

		if ($array[$x] == "STATUS")
			status($row->$fieldname);
		else if($array[$x] == "PROBLEM"){
			echo "<td>";
			link_to_problem($row->$fieldname);
		}
		else{
			echo "<td>";
			echo $row->$fieldname;
		}
			
		echo "</td>";
		   }
		   echo"</tr>";
		}
		echo "</table>";
	}
	
	function problemStatus($status){
		echo "<td bgcolor=";
		if($status == "DONE") echo "\"lime\">";
		else if($status == "NEED FIX") echo "\"red\">";
		else if($status == "NEED OPTIMIZING") echo "\"orange\">";
		else if($status == "NEED CODE QUALITY CHECK") echo "\"yellow\">";
		else echo "\"violet\">";
		echo $status;
	}
	
	function print_user_problems($owner){
		$db_handle = connect();
		if ($owner != "ALL")
		    $query = "SELECT sectionName AS Kategoria, problemID AS Problem, problemStatus AS Status FROM PROBLEM JOIN SECTION USING(sectionID)
			WHERE owner = '$owner' ORDER BY status_priority(problemStatus) DESC, problemName ASC;";
		else $query = "SELECT sectionName AS Kategoria, problemID AS Problem, problemStatus AS Status FROM PROBLEM JOIN SECTION USING(sectionID)
			ORDER BY status_priority(problemStatus) DESC, problemName ASC;";
		
		$result = pg_query($db_handle, $query);
		
		// wypisanie wyniku
		$numrows = pg_num_rows($result);
		$fnum = pg_num_fields($result);

		echo "<table border width='100%'>";
		echo "<tr align='center'>";
		
		$array = array();

		for ($x = 0; $x < $fnum; $x++) {
			$array[] = strtoupper(pg_field_name($result, $x));
			echo "<td><b><font size='5'>";
			echo strtoupper(pg_field_name($result, $x));
			echo "</font></b></td>";
		}

		echo "</tr>";

		for ($i = 0; $i < $numrows; $i++) {
		   $row = pg_fetch_object($result, $i);
		   echo "<tr align='center'>";
		   for ($x = 0; $x < $fnum; $x++) {
			$fieldname = pg_field_name($result, $x);
			
			if ($array[$x] == "STATUS")
				problemStatus($row->$fieldname);
			else if($array[$x] == "PROBLEM"){
				echo "<td>";
				link_to_problem($row->$fieldname);
			}
			else{
				echo "<td>";
				echo $row->$fieldname;
			}
			echo "</td>";
		   }
		   echo"</tr>";
		}
		echo "</table>";
	}
	
	function add_new_comment($problemID, $content, $userName){
		$db_handle = connect();
		$query = "INSERT INTO COMMENTS (problemID, message, userName, creationDate) VALUES ($problemID, '$content', '$userName', timeofday() );";
		pg_query($db_handle, $query);
	}
	
	function get_array_of_comments($problemID){
		$db_handle = connect();
		$query = "SELECT userName, creationDate, message FROM COMMENTS WHERE problemID = $problemID ORDER BY -commentID;";
		$result = pg_query($db_handle, $query);
		
		$numrows = pg_num_rows($result);
		$res = array();
		for($i=0; $i<$numrows; $i++){
			$res[] = array( pg_fetch_result($result, $i, 0),  pg_fetch_result($result, $i, 1),  pg_fetch_result($result, $i, 2));
		}
		return $res;
	}
?>
